import sqlite3
import os
from weibosender.utils.path_utils import PathUtils

class SqlHelper:
    # global var
    # 是否打印sql
    SHOW_LOG = False

    # private var
    # 数据库连接路径
    _connection_string = PathUtils.root_path + '/weibosender.data'

    @staticmethod
    def get_connection():
        connection = None
        if os.path.exists(SqlHelper._connection_string) and \
                os.path.isfile(SqlHelper._connection_string):
            connection = sqlite3.connect(SqlHelper._connection_string)
        else:
            print('连接数据库失败：未找到数据库文件！')
        return connection

    '''关闭数据库游标对象和数据库连接对象'''
    @staticmethod
    def close(connection):
        try:
            if connection is not None:
                connection.close()
        finally:
            if connection is not None:
                connection.close()

    '''查询所有数据'''

    @staticmethod
    def fetchall(sql, params=None):
        cursor = SqlHelper.execute_query(sql, params)
        try:
            if cursor:
                return cursor.fetchall()
        except Exception as e:
            print('fetchall执行异常 > sql:%s，parameters:%s\nException:%s' % (sql, params and params or '', e))
        finally:
            SqlHelper.close(cursor)
        return None

    '''查询一条数据'''
    @staticmethod
    def fetchone(sql, params=None):
        cursor = SqlHelper.execute_query(sql, params)
        try:
            if cursor:
                return cursor.fetchone()
        except Exception as e:
            print('fetchone执行异常 > sql:%s，parameters:%s\nException:%s' % (sql, params and params or '', e))
        finally:
            SqlHelper.close(cursor)
        return None

    '''
    执行 SQL 语句
    RESULT: 返回一个数值表示执行后影响的行数
    '''
    @staticmethod
    def execute(sql, params=None):
        connection = SqlHelper.execute_non_query(sql, params)
        try:
            if connection:
                connection.commit()
                return connection.total_changes
        except Exception as e:
            connection.rollback()
            print('execute执行异常 > sql:%s，parameters:%s\nException:%s' % (sql, params and params or '', e))
        finally:
            SqlHelper.close(connection)
        return -1

    @staticmethod
    def execute_query(sql, params=None):
        if sql is not None and sql != '':
            connection = SqlHelper.get_connection()
            if not connection:
                return None

            try:
                if params is not None:
                    if SqlHelper.SHOW_LOG:
                        print('Execute sql:%s,parameters:%s' % (sql, params))
                    cursor = connection.execute(sql, params)
                else:
                    cursor = connection.execute(sql)
                return cursor
            except Exception as e:
                print('execute_query执行异常 > sql：%s,parameters:%s\nException:%s' % (sql,params and params or '', e))
        else:
            print('the %s is empty or equal None!' % sql)

        return None

    @staticmethod
    def execute_non_query(sql, params=None):
        if sql is not None and sql != '':
            connection = SqlHelper.get_connection()
            if not connection:
                return None
            try:
                if params is not None:
                    if SqlHelper.SHOW_LOG:
                        print('Execute sql:%s,parameters:%s' % (sql, params))
                    connection.execute(sql, params)
                else:
                    connection.execute(sql)
                return connection
            except Exception as e:
                print('execute_non_query执行异常 > sql：%s,parameters:%s\nException:%s' % (sql,params and params or '', e))
        else:
            print('the %s is empty or equal None!' % sql)

        return None
